package com.yayan.beautyclub.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.mysql.jdbc.Statement;
import com.yayan.beautyclub.bean.Category;

public class CategoryDao extends ConnectionBase implements ICategoryDao {

	public CategoryDao(){}
	
	@Override
	public List<Category> getCategoryList() throws Exception{
		// TODO Auto-generated method stub
		QueryRunner qRunner = new QueryRunner();
		String queryStr = "select * from category where status=1 and (parentId=0 or parentId in";
		queryStr += " (select cId from category where parentId=0 and status=1)) order by parentId asc,cId asc";
		List<Category> list = (List<Category>) qRunner.query(getConn(), queryStr, new BeanListHandler(Category.class));
		return list;
	}

	@Override
	public int saveCategory(Category category) throws Exception{
		// TODO Auto-generated method stub
		String sql = "insert into category(cName,introduce,image,time,parentId) values(?,?,?,?,?)";
		Connection conn = getConn();
		conn.setAutoCommit(false);       //不自动提交  
		PreparedStatement ps= conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
		ps.setString(1, category.getcName());
		ps.setString(2, category.getIntroduce());
		ps.setString(3, category.getImage());
		ps.setString(4, new java.sql.Timestamp(category.getTime().getTime()).toString());		
		ps.setInt(5, category.getParentId());
		int re = ps.executeUpdate();
		ResultSet rsKey = ps.getGeneratedKeys();
		int r = 0;
		while(re != 0 && rsKey.next())
		{
			r = rsKey.getInt(1);
		}
		conn.commit();
		conn.setAutoCommit(true);     //设回自动提交
		close(conn,ps,rsKey);
		return r;
	}

	@Override
	public boolean update(Category category) throws Exception{
		// TODO Auto-generated method stub
		String sql = "update category set cName=?,introduce=?,image=?,time=?,parentId=? where cId=?";
		Connection conn = getConn();
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, category.getcName());
		ps.setString(2, category.getIntroduce());
		ps.setString(3, category.getImage());		
		ps.setString(4, new java.sql.Timestamp(category.getTime().getTime()).toString());
		ps.setInt(5, category.getParentId());
		ps.setLong(6, category.getcId());
		boolean r = ps.executeUpdate() > 0 ? true:false;
		close(conn,ps,null);
		return r;
	}

	@Override
	public Category getCategory(int cId) throws Exception {
		// TODO Auto-generated method stub
		String sqlStr = "select * from category where cId=? and status=1";
		QueryRunner qRunner = new QueryRunner();
		Category category = (Category) qRunner.query(getConn(), sqlStr, new BeanHandler(Category.class), cId);
		return category;
	}

	@Override
	public boolean delete(int cId) throws Exception {
		// TODO Auto-generated method stub
		String sqlStr = "update category set status=0 where cId=?";
		Connection conn = getConn();
		PreparedStatement ps = conn.prepareStatement(sqlStr);
		ps.setInt(1, cId);
		boolean res = ps.executeUpdate() > 0 ? true : false;
		close(conn,ps,null);
		return res;
	}

	@Override
	public List<Category> getCategoryList(int parentId) throws Exception {
		// TODO Auto-generated method stub
		QueryRunner qRunner = new QueryRunner();
		String queryStr = "select * from category where parentId=? and status=1";		
		List<Category> list = (List<Category>) qRunner.query(getConn(), queryStr, new BeanListHandler(Category.class), parentId);
		return list;
	}

}
